

'''

-- 创建用户 - 角色关联表
CREATE TABLE user_roles (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (role_id) REFERENCES roles(role_id),
    UNIQUE (user_id, role_id)
);
'''

from . import logger
import psycopg2

class UserRolesTable:

    def __init__(self, connection, cursor):

        self.connection = connection
        self.cursor = cursor


    # 创建用户 - 角色关联
    def create_user_role(self, user_id, role_id):
        
        try:
            self.cursor.execute("INSERT INTO user_roles (user_id, role_id) VALUES (%s, %s)", (user_id, role_id))


        except psycopg2.errors.UniqueViolation as e:
            logger.warning("User-role already exists")

            return True, "User-role already exists"

        except Exception as e:
            logger.error("Error creating user-role: {}".format(e))
            return False, str(e)

        else:

            logger.info(f"User-role {user_id} - {role_id} created successfully.")
            return True, "User-role created successfully"


    # 根据用户id和角色id查询是否存在关联关系
    def is_user_role_exist(self, user_id, role_id) -> bool:
        
        try:
            self.cursor.execute("SELECT * FROM user_roles WHERE user_id = %s AND role_id = %s", (user_id, role_id))
            user_role = self.cursor.fetchone()

        except Exception as e:
            logger.error("Error getting user-role: {}".format(e))
            user_role = None

        else:
            logger.info(f"User-role {user_id} - {role_id} retrieved successfully.")

        return bool(user_role)
    

    # 根据用户id查询角色id列表
    def get_role_ids_by_user_id(self, user_id) -> list:
        
        try:
            self.cursor.execute("SELECT role_id FROM user_roles WHERE user_id = %s", (user_id,))
            role_ids = [roles["role_id"] for roles in self.cursor.fetchall()]

        except Exception as e:
            logger.error("Error getting role ids by user id: {}".format(e))
            role_ids = []

        else:
            logger.info(f"Role ids of user {user_id} retrieved successfully.")

        return role_ids
    

    # 根据角色id查询用户id列表
    def get_user_ids_by_role_id(self, role_id) -> list:
        
        try:
            self.cursor.execute("SELECT user_id FROM user_roles WHERE role_id = %s", (role_id,))
            user_ids = [users["user_id"] for users in self.cursor.fetchall()]

        except Exception as e:
            logger.error("Error getting user ids by role id: {}".format(e))
            user_ids = []

        else:
            logger.info(f"User ids of role {role_id} retrieved successfully.")

        return user_ids
    

    # 根据用户id, 角色id删除角色关联
    def delete_user_role(self, user_id, role_id):
        
        try:
            self.cursor.execute("DELETE FROM user_roles WHERE user_id = %s AND role_id = %s", (user_id, role_id))

        except Exception as e:

            logger.error("Error deleting user-role: {}".format(e))

        else:
            logger.info(f"User-role {user_id} - {role_id} deleted successfully.")


    # 根据用户id删除所有角色关联
    def delete_all_user_roles(self, user_id):
        
        try:
            self.cursor.execute("DELETE FROM user_roles WHERE user_id = %s", (user_id,))

        except Exception as e:

            logger.error("Error deleting all user-roles: {}".format(e))

        else:
            logger.info(f"All user-roles of user {user_id} deleted successfully.")


    # 根据角色id删除所有用户关联
    def delete_all_user_roles_by_role_id(self, role_id):
        
        try:
            self.cursor.execute("DELETE FROM user_roles WHERE role_id = %s", (role_id,))

        except Exception as e:

            logger.error("Error deleting all user-roles by role id: {}".format(e))

        else:
            logger.info(f"All user-roles of role {role_id} deleted successfully.")


    